package com.sneakxy.jdbc;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.lang3.StringUtils;

import com.sneakxy.config.Jdbc;

import oracle.jdbc.OracleConnection;

/**
 * @author 潜行的青衣
 * @see
 */
public class JdbcHelper {
	
	private final static Map<Integer, Class<?>> TYPES = new HashMap<>();
	
	private final static Map<Integer, String> TYPE_NAMES = new HashMap<>();
	
	static {
		TYPES.put(Types.BIGINT, Long.class);
		TYPES.put(Types.BOOLEAN, Boolean.class);
		TYPES.put(Types.BIT, Boolean.class);
		TYPES.put(Types.CHAR, String.class);
		TYPES.put(Types.VARCHAR, String.class);
		TYPES.put(Types.DOUBLE, Double.class);
		TYPES.put(Types.FLOAT, Float.class);
		TYPES.put(Types.DATE, String.class);
		TYPES.put(Types.DECIMAL, Integer.class);
		TYPES.put(Types.INTEGER, Integer.class);
		TYPES.put(Types.NUMERIC, BigDecimal.class);
		TYPES.put(Types.TIMESTAMP, String.class);
		TYPES.put(Types.BINARY, byte[].class);
		TYPES.put(Types.TINYINT, Short.class);
		
		TYPE_NAMES.put(Types.BIGINT, "BIGINT");
		TYPE_NAMES.put(Types.BOOLEAN, "BOOLEAN");
		TYPE_NAMES.put(Types.BIT, "BIT");
		TYPE_NAMES.put(Types.CHAR, "CHAR");
		TYPE_NAMES.put(Types.VARCHAR, "VARCHAR");
		TYPE_NAMES.put(Types.DOUBLE, "DOUBLE");
		TYPE_NAMES.put(Types.FLOAT, "FLOAT");
		TYPE_NAMES.put(Types.DATE, "DATE");
		TYPE_NAMES.put(Types.DECIMAL, "DECIMAL");
		TYPE_NAMES.put(Types.INTEGER, "INTEGER");
		TYPE_NAMES.put(Types.NUMERIC, "NUMERIC");
		TYPE_NAMES.put(Types.TIMESTAMP, "TIMESTAMP");
		TYPE_NAMES.put(Types.BINARY, "BINARY");
		TYPE_NAMES.put(Types.TINYINT, "TINYINT");
	}
	
	public interface ResultDealable<T> {
		
		public List<T> onResultSetCallback(ResultSet rs) throws SQLException;
		
	}
	
	private Jdbc jdbc;
	
	private Connection conn;

	private Dialect dialect;
	
	public JdbcHelper(Jdbc jdbc, Dialect dialect) {
		this.jdbc = jdbc;
		this.dialect = dialect;
	}
	
	public static Map<Integer, Class<?>> getSqlTypeConverter() {
		return TYPES;
	}
	
	public static Class<?> getJavaType(Integer type) {
		Class<?> result = TYPES.get(type);
		return result == null ? String.class : result;
	}
	
	public static String getSqlTypeName(Integer type) {
		String result = TYPE_NAMES.get(type);
		return result == null ? "" : result;
	}
	
	public void openConnection() throws SQLException, ClassNotFoundException {
		this.openConnection(true);
	}
	
	public void openConnection(boolean autoCommit) throws SQLException, ClassNotFoundException {
		try {
			Class.forName(jdbc.getDriver());
			Properties props = new Properties();
			props.put("user", jdbc.getUsername());
			props.put("password", jdbc.getPassword());
			this.conn = DriverManager.getConnection(jdbc.getUrl(), props);
			if(this.conn instanceof OracleConnection) {
				((OracleConnection) this.conn).setRemarksReporting(true);
			}
			this.conn.setAutoCommit(autoCommit);
		} catch (ClassNotFoundException e) {
			System.out.println("openConnection error!");
			throw e;
		} catch (SQLException e) {
			System.out.println("openConnection error!");
			throw e;
		}
	}
	
	public <T> List<T> execute(String sql, ResultDealable<T> dealable) throws SQLException {
		PreparedStatement psm = null;
		ResultSet rs = null;
		try {
			psm = this.conn.prepareStatement(sql);
			rs = psm.executeQuery();
			return dealable.onResultSetCallback(rs);
		} catch(SQLException e) {
			System.out.println("PreparedStatement close error!");
			throw e;
		} finally {
			if(rs != null) {
				rs.close();
			}
			if(psm != null) {
				psm.close();
			}
		}
	}
	
	public static interface ColumnMetaDataHandler {
		
		public void handler(String columnName, int columnType, String comment);
		
	}
	
	public void columnMetaData(String tableName, ColumnMetaDataHandler handler) throws Exception {
		String table = tableName;
		if(StringUtils.contains(tableName, ".")) {
			table = StringUtils.split(tableName, ".")[1];
		}
		ResultSet rs = conn.getMetaData().getColumns(
				getDialect().getCatalog(jdbc, tableName), 
				getDialect().getSchema(jdbc, tableName), table, "%");
		try {
			while(rs.next()) {
				handler.handler(rs.getString("COLUMN_NAME"), rs.getInt("DATA_TYPE"), rs.getString("REMARKS"));
			}
		} finally {
			rs.close();
		}
	}
	
	public int update(String sql) throws SQLException {
		PreparedStatement psm = null;
		ResultSet rs = null;
		try {
			psm = this.conn.prepareStatement(sql);
			return psm.executeUpdate(sql);
		} catch(SQLException e) {
			System.out.println("PreparedStatement close error!");
			throw e;
		} finally {
			if(rs != null) {
				rs.close();
			}
			if(psm != null) {
				psm.close();
			}
		}
	}
	
	public ResultSet getPrimaryKey(String tableName) throws SQLException {
		String table = tableName;
		if(StringUtils.contains(tableName, ".")) {
			table = StringUtils.split(tableName, ".")[1];
		}
		return this.conn.getMetaData().getPrimaryKeys(getDialect().getCatalog(jdbc, tableName), getDialect().getSchema(jdbc, tableName), table);
	}
	
	
	public void commit() throws SQLException {
		try {
			this.conn.commit();
		} catch (SQLException e) {
			System.out.println("connection close error!");
			throw e;
		}
	}
	
	public void close() {
		if(conn != null) {
			try {
				conn.close();
				conn = null;
			} catch (SQLException e) {
				System.out.println("connection close error!");
			}
		}
	}

	public Dialect getDialect() {
		return dialect;
	}

	public Jdbc getJdbc() {
		return jdbc;
	}

	public Connection getConnection() {
		return conn;
	}

}
